CREATE TABLE FISH_PHOTOS 
(
  FP_ID_PICTURE NUMBER(5, 0) NOT NULL 
, FP_FILE_NAME VARCHAR2(50 CHAR) 
, FP_IMAGE ORDSYS.ORDIMAGE 
, FP_ID_FISH NUMBER(5, 0) 
, FP_STILLIMAGE ORDSYS.SI_STILLIMAGE 
, CONSTRAINT SYS_C0011154 PRIMARY KEY 
  (
    FP_ID_PICTURE 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  NEXT 1048576 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE FISH_OCCURRENCE 
(
  FO_OCCUR_ID NUMBER(5, 0) NOT NULL 
, FO_COUNTRY_NAME VARCHAR2(40 CHAR) 
, FO_FISH_DATA_ID NUMBER(5, 0) 
, CONSTRAINT FISH_OCCURRENCE_PK PRIMARY KEY 
  (
    FO_OCCUR_ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  NEXT 1048576 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE FISH_DATA 
(
  FD_ID_FISH NUMBER(5, 0) NOT NULL 
, FD_FISH_NAME VARCHAR2(30 CHAR) 
, FD_FISH_KIND VARCHAR2(30 CHAR) 
, FD_LENGTH VARCHAR2(20 CHAR) 
, FD_TEMPERATURE VARCHAR2(10 CHAR) 
, FD_PH NUMBER(2, 1) 
, FD_FOOD VARCHAR2(50 CHAR) 
, FD_LATIN_NAME VARCHAR2(30 CHAR) 
, CONSTRAINT FISH_DATA_PK PRIMARY KEY 
  (
    FD_ID_FISH 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  NEXT 1048576 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

ALTER TABLE FISH_PHOTOS
ADD CONSTRAINT FISH_PHOTOS_FISH_DATA_FK1 FOREIGN KEY
(
  FP_ID_FISH 
)
REFERENCES FISH_DATA
(
  FD_ID_FISH 
)
ENABLE;

ALTER TABLE FISH_OCCURRENCE
ADD CONSTRAINT FISH_OCCURRENCE_FISH_DATA_FK1 FOREIGN KEY
(
  FO_FISH_DATA_ID 
)
REFERENCES FISH_DATA
(
  FD_ID_FISH 
)
ENABLE;

CREATE SEQUENCE SEQ_FISH_PHOTO INCREMENT BY 1 MAXVALUE 99999 MINVALUE 1 CACHE 20;

CREATE SEQUENCE SEQ_FISH_OCCUR INCREMENT BY 1 MAXVALUE 99999 MINVALUE 1 CACHE 20;

CREATE SEQUENCE SEQ_FISH_DATA INCREMENT BY 1 MAXVALUE 99999 MINVALUE 1 CACHE 20;

CREATE PROCEDURE updateStillImage 
( row_id IN number)
IS
OBRAZEK_SI SI_StillImage;
ord_image OrdImage;
BEGIN
SELECT FP_IMAGE INTO ord_image FROM fish_photos WHERE fp_id_picture=row_id FOR UPDATE;
ord_image.setProperties();
OBRAZEK_SI := new SI_StillImage(ord_image.getContent());

update fish_photos fp 
set fp.fp_stillimage = OBRAZEK_SI
where fp.FP_ID_PICTURE = row_id; 
END;
/

CREATE PROCEDURE load_fish_from_dir 
( name_in IN varchar2, dir_in IN varchar2)
IS
obrazek ORDSYS.ORDImage;
ctx RAW(64) := NULL;
row_id urowid;
BEGIN
INSERT INTO fish_photos (fp_id_picture, fp_file_name, fp_image)
VALUES (SEQ_FISH_PHOTO.NEXTVAL,name_in, ORDImage.init('FILE',dir_in,name_in))
RETURNING fp_image,rowid INTO obrazek, row_id;
obrazek.import(ctx); -- ORDImage.import wywołuje ORDImage.setProperties;
UPDATE fish_photos SET fp_image = obrazek WHERE rowid = row_id; --
COMMIT;
END;
/

CREATE TRIGGER TRG_CREATE_STILLIMAGE after INSERT ON FISH_PHOTOS
REFERENCING NEW AS n                 -- new manager information
FOR EACH ROW
DECLARE
OBRAZEK_SI SI_StillImage;
ord_image OrdImage;
row_id number(5,0);
ctx RAW(64) := NULL;
BEGIN
row_id := :n.FP_ID_PICTURE;
ord_image := :n.FP_IMAGE;

ord_image.setProperties();
OBRAZEK_SI := new SI_StillImage(ord_image.getContent());

--update fish_photos fp 
--fp.fp_stillimage = OBRAZEK_SI
--set fp.fp_id_fish = 1
--where fp.FP_ID_PICTURE = row_id; 
END;
/

ALTER TRIGGER TRG_CREATE_STILLIMAGE DISABLE;
